package cn.com.utt.unms.mapper;

import java.util.Collection;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import cn.com.utt.unms.pojo.Group;
import cn.com.utt.unms.pojo.Network;
import cn.com.utt.unms.vo.NetworkInfo;

@Mapper
public interface NetworkMapper {
	
	@Select("select g.* from device d, `group` g where d.user_id=g.user_id and d.id=#{deviceid}")
	List<Network> queryGroupsByDeviceId(@Param("deviceid")Long deviceid);
	
	@Select("select n.* from device d, `network` n where d.network_id=n.id and d.id=#{deviceId}")
	Network queryByDeviceId(@Param("deviceId")Long deviceId);
	
	@Select({"<script>",
    "SELECT *", 
    "FROM `network` n ",
    "WHERE ", 
    "id IN", 
      "<foreach item='item' index='index' collection='networkIds'",
        "open='(' separator=',' close=')'>",
        "#{item}",
      "</foreach>",
      "<if test='searchs != null'>",
      "<foreach item='item' index='index' collection='searchs'",
        "open='and' separator='and' close=''>",
        "<choose>",
        "<when test='item.op != \"like\"'>",
        "${item.column}",
        " = ",
        "#{item.value}",
        "</when>",
        "<otherwise>",
        "${item.column}",
        " like ",
        "'%${item.value}%'",
        "</otherwise>",
        "</choose>",
      "</foreach>",
      "</if>",
      "limit #{start},#{rows}",
    "</script>"})
	List<Network> queryByCondition(Map<String, Object> conditions);
	
	@Insert("insert into `network`(`name`,`group`,group_id,createtime,remark,location_id) values(#{name},#{group},#{groupId},now(),#{remark},#{locationId})")
	@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
	void save(Network network);
	
	@Update("update `network` set `name`=#{name},remark=#{remark},`group`=#{group},group_id=#{groupId} where id=#{id}")
	void update(Network network);
	
	@Delete({"<script>",
	    "delete ", 
	    "FROM `network`",
	    "WHERE ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	void delete(@Param("networkIds")List<Long> networkIds);
	
	@Select("select * from `group` where user_id=#{userid} limit #{start},#{rows}")
	List<Group> queryGroupsByPage(@Param("start")int start,@Param("rows")int rows,@Param("userid")Long userid);
	
	@Select("select * from `network` where id=#{id}")
	Network queryById(@Param("id")Long id);
	
	@Select("select * from `network` where group_id=#{groupId}")
	List<Network> queryByGroupId(@Param("groupId")Long groupId);
	
	@Select({"<script>",
	    "SELECT id", 
	    "FROM `network`",
	    "WHERE ", 
	    "group_id IN", 
	      "<foreach item='item' index='index' collection='groupIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	List<Long> queryByGroupIds(@Param("groupIds")List<Long> groupIds);
	
	@Select("select * from `group` where binary name=#{name} and user_id=#{userId}")
	Group queryByNameAndUserId(@Param("name")String name,@Param("userId")long userId);
	
	@Select({"<script>",
	    "select t1.*,IFNULL(t2.deviceCount,0) as deviceCount from (select g.*,",
	    "<choose>",
	    "<when test='parentId == 0'>",
	    		"'' as parentName,",
	    "</when>",
	    "<otherwise>",
	    		"g1.name as parentName,",
	    "</otherwise>",
	    "</choose>",
	    "u.username from `group` g,`user` u",
	    "<if test='parentId != 0'>",
	      ",`group` g1",
	    "</if>",
	    " where g.user_id=u.id",
	    "<if test='parentId != 0'>",
	    		" and g.parent_id=g1.id",
	    	"</if>",
	    " and g.id=#{id}) t1 left join (select g.id,count(d.group_id) as deviceCount from `device` d,`group` g where d.group_id=g.id group by d.group_id) t2 on t1.id=t2.id",
	    "</script>"})
	@Results({  
		@Result(column="id", property="id"),  
		@Result(column="user_id", property="userId"),  
		@Result(column="parent_id", property="parentId"), 
		@Result(column="name", property="name"),
		@Result(column="deviceCount", property="deviceCount"),
		@Result(column="parentName", property="parentName"),  
		@Result(column="username", property="manager"),  
		@Result(column="createtime", property="createtime"),
		@Result(column="location_id", property="locationId"),
	})  
	NetworkInfo queryGroupInfo(Group group);
	
	@Select({"<script>",
	    "select g.location_id,count(d.id) as total from `group` g,device d ", 
	    "where d.group_id = g.id and ", 
	    "g.id IN", 
	      "<foreach item='item' index='index' collection='groups'",
	        "open='(' separator=',' close=')'>",
	        "#{item.id}",
	      "</foreach>",
	      " group by g.location_id",
	"</script>"})
	List<Map<String,Long>> queryDeviceCountByGroups(@Param("groups")List<Group> groups);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM network",
	    "WHERE ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	List<Network> queryByIds(@Param("networkIds")Collection<Long> networkIds);
	
	@Update({"<script>",
	    "update `network` set `group`=#{group} ", 
	    "WHERE ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	void updateGroup(@Param("networkIds")List<Long> networkIds,@Param("group")String group);
	
	@Select({"<script>",
	    "SELECT COUNT(*) AS total", 
	    "FROM `network` n ",
	    "WHERE 1=1 and ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      "<if test='searchs != null'>",
	      "<foreach item='item' index='index' collection='searchs'",
	        "open='and' separator='and' close=''>",
	        "<choose>",
	        "<when test='item.op != \"like\"'>",
	        "${item.column}",
	        " = ",
	        "#{item.value}",
	        "</when>",
	        "<otherwise>",
	        "${item.column}",
	        " like ",
	        "'%${item.value}%'",
	        "</otherwise>",
	        "</choose>",
	      "</foreach>",
	      "</if>",
	    "</script>"})
		int queryCountByCondition(Map<String, Object> conditions);
	
	@Select({"<script>",
	    "SELECT id", 
	    "FROM network",
	    "WHERE ", 
	    "location_id IN", 
	      "<foreach item='item' index='index' collection='locationIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	List<Long>  queryByLocation(@Param("locationIds")List<Long> locationIds);
	
}
